#!/usr/bin/env python3

import os
import sys
import cx_Oracle
import pdb
import click
import logging
import yaml
from threading import Thread
import queue

ora_username='eular_dev'
ora_password='Paas0426'
ora_conn_str='172.32.148.156:1521/orcl'
# database link name
ora_dblink='my_db_link'

# 创建日志目录，可以通过环境变量MD5LOGDIR指定
log_file = './workers.log'

# create and truncate log
with open(log_file, 'w') as f:
    pass

logging.basicConfig(level=logging.INFO,
                    format='%(levelname)s[%(asctime)s]:%(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S',
                    filename=log_file,
                    filemode='a+')

class MyThread(Thread):
    def __init__(self, idx, que, args):
        super(MyThread, self).__init__()
        self.func = self.insert_worker
        self.args = args
        self.que = que
        self.idx = idx

    # the handler enables code to retrieve clob or blob value like ordinary columns
    def output_type_handler(self, cursorsor, name, default_type, size, precision, scale):
        if default_type == cx_Oracle.DB_TYPE_CLOB:
            return cursorsor.var(cx_Oracle.DB_TYPE_LONG, arraysize=cursorsor.arraysize)
        if default_type == cx_Oracle.DB_TYPE_BLOB:
            return cursorsor.var(cx_Oracle.DB_TYPE_LONG_RAW, arraysize=cursorsor.arraysize)

    # insert worker function
    # get table names from queue and do inserting until queue is empty
    def insert_worker(self, username, password, conn_str, dblink):
        retdict = {}
        logging.info('Thread {} is started.'.format(self.idx))
        connection=cx_Oracle.connect(username, password, conn_str)
        connection.outputtypehandler = self.output_type_handler
        cursor=connection.cursor()
        cursor.prefetchrows = 500
        cursor.arraysize = 100
        try:
            while True:
                tab = self.que.get(block=True, timeout=3)
                sql = 'INSERT INTO /*+ append */ {} NOLOGGING SELECT * FROM {}@{}'.format(tab, tab, dblink)
                logging.info('Thread {} : {}'.format(self.idx, sql))
                cursor.execute(sql)
                connection.commit()
        except queue.Empty:
            logging.info('Queue is Empty, thread {} finished.'.format(self.idx))
        connection.close()
        return retdict

    def run(self):
        self.result = self.func(*self.args)

    def get_result(self):
        try:
            return self.result
        except Exception:
            return None


@click.group(context_settings=dict(help_option_names=['-h', '--help']))
def main():
    pass

@main.command('ora', help='Start multiple workers to insert, logging to workers.log', no_args_is_help=True)
@click.option('--workers', '-w', default=1, help='Worker threads')
@click.option('--input-file', '-i', help='Intput file')
@click.option('--table-list', '-t', help='Input table list e.g. -t "tb1,tb2,tb3"')
def ora(workers, input_file, table_list):
    # populate queue from input
    q = queue.Queue()
    if table_list:
        for tabname in table_list.split(','):
            if len(tabname) > 0:
                q.put(tabname)
    elif input_file:
        with open(input_file, 'r', encoding="utf-8") as f:
            while True:
                tabname = f.readline().strip()
                if len(tabname) > 0:
                    q.put(tabname)
                else:
                    break;
    else:
        raise Exception('Error: No input tables.')
    # start workers
    thd_list = []
    for idx in range(0, workers):
        thd = MyThread(idx, q, (ora_username, ora_password, ora_conn_str, ora_dblink))
        thd.start()
        thd_list.append(thd)
    # waiting for thread finished.
    for t in thd_list:
        t.join()

if __name__ == '__main__':
    main(None)

